package com.etc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import com.etc.dao.OrdersDao;
import com.etc.dto.OrderCountDto;
import com.etc.entity.Items;
import com.etc.entity.Orders;
import com.etc.util.DBUtil;
import com.etc.util.PageData;

/**
 * @OrdersDaoImpl
 * 
 * @author knowno
 *
 */
public class OrdersDaoImpl implements OrdersDao {

	/**
	 * 新增订单
	 */
//	@Override
//	public boolean addOrders(Orders orders) {
//		String sql = "insert into tbl_order values(null,?,?,?,?,?)";
//		return DBUtil.exUpdate(sql, orders.getOrdersum(), orders.getOrderdate(), orders.getOrderstate(),
//				orders.getUserid(), orders.getTotal()) > 0;
//	}

	/**
	 * 删除订单
	 */
	@Override
	public boolean delOrders(int orderid) {
		String sql = "delete from tbl_order where orderid=?";
		return DBUtil.exUpdate(sql, orderid) > 0;
	}

	/**
	 * 修改订单信息
	 */
	@Override
	public boolean upOrders(int ordersum, int orderstate, double total, int orderid) {
		String sql = "update tbl_order set ordersum=?,orderstate=?,total=? where orderid=?";
		return DBUtil.exUpdate(sql, ordersum, orderstate, total, orderid) > 0;
	}

	/**
	 * 修改订单状态
	 */
	@Override
	public boolean upOrdersState(int orderstate, String orderid) {
		String sql = "update tbl_order set orderstate =? where orderid=?";
		return DBUtil.exUpdate(sql, orderstate, orderid) > 0;
	}

	/**
	 * 用户查看订单
	 */
	@Override
	public PageData<Orders> queryOrdersUserid(int page, int pageSize, int userid) {
		String sql = "select * from tbl_order where userid=?";
		return DBUtil.exQueryByPage(sql, Orders.class, page, pageSize, userid);
	}

	/**
	 * 根据订单状态查询
	 */

	@Override
	public PageData<Orders> queryOrdersState(int page, int pageSize, int orderstate) {
		String sql = "select * from tbl_order where orderstate=?";
		return DBUtil.exQueryByPage(sql, Orders.class, page, pageSize, orderstate);
	}

	/**
	 * 查看订单详情
	 */
	@Override
	public Orders queryOneOrders(int orderid) {
		String sql = "select * from tbl_order where orderid = ?";
		List<Orders> list = (List<Orders>) DBUtil.exQuery(sql, Orders.class, orderid);
		if (list.size() == 0) {
			return null;
		}
		return list.get(0);
	}

	/**
	 * (non-Javadoc)
	 * 
	 * <p>
	 * Title: getByPage
	 * </p>
	 * 
	 * <p>
	 * Description:
	 * </p>
	 * 
	 * @param keywords
	 * @param begindate
	 * @param enddate
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * 
	 * @see com.etc.zmall.dao.OrdersDao#getByPage(java.lang.String,
	 *      java.lang.String, java.lang.String, int, int)
	 * 
	 */
	@Override
	public PageData<Orders> getByPage(String keywords, String begindate, String enddate, int pageNo, int pageSize) {

		String sql = "SELECT *  from tbl_order where orderid like ? and (orderdate >=? and orderdate <=DATE_ADD(?,INTERVAL 1 DAY)) order by orderdate desc";
		PageData<Orders> pd = DBUtil.exQueryByPage(sql, Orders.class, pageNo, pageSize, "%" + keywords + "%", begindate,
				enddate);
		return pd;
	}

	/**
	 * @我们发现，如果表连接查询，导致订单数量单独计算，给属性进行赋值的时候，也会比较复杂 还是要拆分成: 1先查订单
	 */
	public PageData<Orders> getOrderAndItemsByPage(String keywords, String begindate, String enddate, int pageNo,
			int pageSize) {
		// TODO Auto-generated method stub
		String sql = "SELECT tbl_order.orderid,tbl_order.ordersum,tbl_order.orderdate,tbl_order.orderstate,tbl_order.userid,tbl_order.total,tbl_items.itemsid,tbl_items.count,tbl_items.goodsid,tbl_items.shopid,tbl_items.cost FROM tbl_order INNER JOIN tbl_items ON tbl_order.orderid=tbl_items.orderid WHERE tbl_order.orderid LIKE ?";

		PageData<Orders> pd = null;
//		if (!"".equals(begindate) && !"".equals(enddate)) {
//			sql = "SELECT *  from tbl_order where orderid like ? and (orderdate >=? and orderdate <=?)";
//			pd = DBUtil.exQueryByPage(sql, Orders.class, pageNo, pageSize, "%" + keywords + "%",
//					begindate, enddate);
//		} else {
//			pd = DBUtil.exQueryByPage(sql, Orders.class, pageNo, pageSize, "%" + keywords + "%");
//		}

		pd = DBUtil.exQueryByPage(sql, Orders.class, pageNo, pageSize, "%" + keywords + "%");
		return pd;
	}

	/**
	 * (non-Javadoc)
	 * 
	 * <p>
	 * Title: addOrder
	 * </p>
	 * 
	 * <p>
	 * Description: tbl_order,tbl_items,tbl_shopc,"tbl_goods更新库存" ; Spring->
	 * </p>
	 * 
	 * @param orders
	 * @return
	 * 
	 * @see com.etc.zmall.dao.OrdersDao#addOrder(com.etc.zmall.entity.Orders)
	 * 
	 */
	@Override
	public boolean addOrder(Orders orders) {
		// 得到订单项
		List<Items> items = orders.getItems();

		Connection conn = null;

		conn = DBUtil.getConn();

		PreparedStatement pstmt = null;

		// 1 -》插入订单表sql
		String orderSql = "INSERT INTO `zmall`.`tbl_order`(`orderid`, `ordersum`, `orderdate`, `orderstate`, `userid`, `total`) VALUES (?,?, now(), 0, ?, ?)";

		// 2-》itemsid 自动增长 插入订单项
		String sql = "INSERT INTO `zmall`.`tbl_items`(`itemsid`, `count`, `goodsid`, `userid`, `shopid`, `orderid`, `cost`) VALUES (null, ?, ?, ?, ?, ?, ?)";

		// 3 ->删除对应的购物车记录
		String delSql = "DELETE FROM `zmall`.`tbl_shopc` WHERE `shopcid` = ?";

		// 总受影响的行
		int total = 0;
		try {
			// ---- 设置事务边界(不再自动提交了)----
			conn.setAutoCommit(false);

			/************** 添加订单对应记录代码 tbl_order 开始 *********************/
			// 订单表处理开始
			pstmt = conn.prepareStatement(orderSql);

			pstmt.setObject(1, orders.getOrderid());
			pstmt.setObject(2, orders.getOrdersum());
			pstmt.setObject(3, orders.getUserid());
			pstmt.setObject(4, orders.getTotal());

			System.out.println(pstmt);
			total += pstmt.executeUpdate();

			/************** 添加订单对应记录代码 结束 *********************/

			/************** 添加订单项对应记录代码 tbl_items 开始 *********************/
			// 创建一个preparedStatement
			pstmt = conn.prepareStatement(sql);

			// 补齐参数

			// items
			for (Items it : items) {
				pstmt.setObject(1, it.getCount());
				pstmt.setObject(2, it.getGoodsid());
				pstmt.setObject(3, it.getUserid());
				pstmt.setObject(4, it.getShopid());
				pstmt.setObject(5, it.getOrderid());
				pstmt.setObject(6, it.getCost());

				// 添加到Statement对象的当前命令列表中
				pstmt.addBatch();

			}
			System.out.println(pstmt);
			// 执行executeBatch作为批处理执行
			int[] result = pstmt.executeBatch();

			// 得到批处理的结果
			for (int i = 0; i < result.length; i++) {
				System.out.println(result[i]);
				total += result[i];

			}
			/************** 添加订单项对应记录代码 结束 *********************/

			/************** 删除购物车对应记录代码 tbl_shopc 开始 *********************/
			// 创建一个preparedStatement
			pstmt = conn.prepareStatement(delSql);

			// 补齐参数

			// items
			for (Items it : items) {
				pstmt.setObject(1, it.getShopcid());
				// 添加到Statement对象的当前命令列表中
				pstmt.addBatch();
			}

			// 执行executeBatch作为批处理执行
			int[] delresult = pstmt.executeBatch();

			// 得到批处理的结果
			for (int i = 0; i < result.length; i++) {
				total += delresult[i];
			}

			/******************* 删除购物车对应记录代码 结束 **************************/

			if (total == 2 * items.size() + 1) {
				// 订单项
				System.out.println("事务提交");
				conn.commit();
				return true;
			} else {
				System.out.println("事务回滚");
				conn.rollback();
				return false;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("事务回滚");
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return false;

			// e.printStackTrace();
		} finally {
			// 释放资源
			try {
				if (pstmt != null) {
					pstmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	@Override
	public List<OrderCountDto> getOrderCount() {
		String sql = "SELECT a.orderdate,ifnull(b.count,0) AS ordercount FROM ( " + 
				"SELECT curdate() AS orderdate UNION ALL\r\n" + 
				"SELECT date_sub(curdate(),INTERVAL 1 DAY) AS orderdate UNION ALL " + 
				"SELECT date_sub(curdate(),INTERVAL 2 DAY) AS orderdate UNION ALL " + 
				"SELECT date_sub(curdate(),INTERVAL 3 DAY) AS orderdate UNION ALL " + 
				"SELECT date_sub(curdate(),INTERVAL 4 DAY) AS orderdate UNION ALL " + 
				"SELECT date_sub(curdate(),INTERVAL 5 DAY) AS orderdate UNION ALL " + 
				"SELECT date_sub(curdate(),INTERVAL 6 DAY) AS orderdate) a LEFT JOIN (SELECT date(orderdate) AS datetime,count(orderid) AS count from tbl_order GROUP BY date(orderdate)) b ON a.orderdate=b.datetime";
		// TODO Auto-generated method stub
		return DBUtil.exQuery(sql, OrderCountDto.class);
	}

}
